package my.job;

import http.HttpClientUtil;

import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

import my.jdbc.DbConUtil;
import my.jdbc.JdbcTemplate;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang.time.DateFormatUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.http.NameValuePair;
import org.apache.http.message.BasicNameValuePair;

import util.ConstantUtil;
import util.DataExchangeUtil;

import bean.AppCenterSdstockBean;
import bean.BfJyys;
import bean.BfJyysDetail;
import bean.BfJyysJson;
import bean.JhysBean;
import bean.JhysIsOk;
import bean.OssDailyTradeInventory;
import bean.OssMonitorInventory;
import bean.OssMonitorTimeInventory;
import bean.Sale;
import bean.StockData;
import bean.ZdStock;

import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.ObjectMapper;

/**
 * 定时任务的工具类
 * 
 * @author gj
 */
public class TimeTaskJobUtil {

	private static final Log log = LogFactory.getLog(TimeTaskJobUtil.class);

	/**
	 * 进货验收同步任务(每小时运行一次,检索三天以内的出库单)
	 */
	public void syncJhysDataJob() {
		// 获取昨天的时间
		Calendar c = Calendar.getInstance();
		c.add(Calendar.DATE, -3);
		String qtime = DateFormatUtils.format(c, "yyyy-MM-dd");
		log.info("...开始执行进货验收同步任务..." + qtime);
		syncJhys(qtime);
	}

	/**
	 * 站级数据同步任务(凌晨1:00 和 下午13:00运行,检索2天以内的站级数据)
	 */
	public void syncStationDataJob() {
		// 获取昨天的时间
		Calendar c = Calendar.getInstance();
		c.add(Calendar.DATE, -2);
		String btime = DateFormatUtils.format(c, "yyyy-MM-dd");
		String etime = DateFormatUtils.format(new Date(), "yyyy-MM-dd");
		log.info("...开始执行站级数据同步任务..." + btime + ":" + etime);
		syncStationData(btime, etime);
	}

	public void syncSjfyDataJob() {
		log.info("...开始执行实际发油同步任务...");
		syncSjfyData("");
	}

	/**
	 * 进货验收自动同步
	 * 
	 * @param qtime
	 */
	public void syncJhys(String qtime) {
		// 查询前天的出库单(未验收)的油站
		String ckdsql = " SELECT CK_ID,STATION_CODE FROM SH_CKD WHERE CONVERT(VARCHAR, inserttime, 23) >= '" + qtime + "' AND xybj <> 1 GROUP BY STATION_CODE,CK_ID ";
		List<Object[]> stationCodeList = JdbcTemplate.queryListFieldInfoByBean(DbConUtil.get162ptCon(), ckdsql, null, DbConUtil.TYPE_NAME_162PT);

		// 存储异常的油站代码
		List<String> stationErrorList = new ArrayList<String>();

		for (Object[] objects : stationCodeList) {

			String stationCode = objects[1].toString();
			String gkStationCode = DataExchangeUtil.getGkStationCode(stationCode);
			String ckdNo = objects[0].toString();

			// 如果该油站代码异常则不执行任何操作
			if (stationErrorList.contains(stationCode)) {
				continue;
			}

			String ipAddress = DataExchangeUtil.getIpAddressByStationCode(stationCode);
			if (StringUtils.isBlank(ipAddress)) {
				// log.error("IP地址未填写,油站代码:" + stationCode);
				stationErrorList.add(stationCode);
				continue;
			}

			if (!DataExchangeUtil.isOkForIpAddressCon(ipAddress)) {
				// 这个IP地址重试2次,还是连不上数据库
				log.error("数据库连接失败, 油站代码: " + stationCode + ",IP地址: " + ipAddress);
				stationErrorList.add(stationCode);
				continue;
			}

			if (!DataExchangeUtil.isOkForStationAndIP(ipAddress, gkStationCode)) {
				// IP地址维护错了(记录日志)
				log.error("IP地址与实际油站代码不一致, 油站代码: " + stationCode + ",IP地址: " + ipAddress);
				stationErrorList.add(stationCode);
				continue;
			}

			// 查询该站下的进货验收和进货验收明细
			String jhysSql = "SELECT b.DeliveryNo deliveryNob,b.CreateTime createTimeb,b.IsDel isDelb,b.DuringSales duringSalesb,b.TranStatus tranStatusb,b.BeginTime beginTimeb,b.EndTime endTimeb,b.*,a.* FROM oss_acceptance_odRegister a LEFT JOIN oss_acceptance_odRegisterInfo b on a.ManualNo = b.ManualNo where 1=1 ";
			jhysSql += " and a.ManualNo = '" + ckdNo + "' ";
			List<JhysBean> jhysInfoList = JdbcTemplate.queryListInfoByFields(DbConUtil.getGsmappCon(ipAddress), jhysSql, JhysBean.class, null, ipAddress);
			if (jhysInfoList == null || jhysInfoList.size() == 0) {
				continue;
			}

			List<String> deliveryNoList1 = new ArrayList<String>();
			List<BfJyysJson> jhysListJson = new ArrayList<BfJyysJson>();

			for (JhysBean jhysBean : jhysInfoList) {
				if (deliveryNoList1.contains(jhysBean.getDeliveryNo())) {
					continue;
				}

				BfJyysJson bfjson = new BfJyysJson();

				BfJyys jhys = DataExchangeUtil.jhysJsonSetValue(stationCode, jhysBean);

				bfjson.setOdRegister(jhys);

				List<BfJyysDetail> jhysDetailList = new ArrayList<BfJyysDetail>();

				for (JhysBean jhysBean1 : jhysInfoList) {
					if (jhysBean.getManualNo().equals(jhysBean1.getDeliveryNob())) {

						BfJyysDetail jhysDetail = DataExchangeUtil.jhysDetailJsonSetValue(jhysBean1);

						jhysDetailList.add(jhysDetail);
					}
				}

				bfjson.setRegisterInfos(jhysDetailList);
				deliveryNoList1.add(jhysBean.getDeliveryNo());
				jhysListJson.add(bfjson);
			}

			// 调用接口对数据进行发送,log.error会将数据写入到数据库
			List<NameValuePair> nvps = new ArrayList<NameValuePair>();
			ObjectMapper mapper = new ObjectMapper();
			String jhysListJsonString = "[]";
			try {
				jhysListJsonString = mapper.writeValueAsString(jhysListJson);
				log.info("TimeTaskJobUtil: ===jhysListJsonString===" + jhysListJsonString);
			} catch (JsonProcessingException e1) {
				e1.printStackTrace();
			}
			nvps.add(new BasicNameValuePair("params", jhysListJsonString));
			String responseMessage = "";
			try {
				responseMessage = HttpClientUtil.sendPost("http://10.190.1.165:8080/bfJyys/insertBfJyys.do", nvps, "进货验收数据");
			} catch (Exception e) {
				log.error("出库单编号:" + ckdNo + ",接口调用失败:" + e);
			}
			log.error("出库单编号:" + ckdNo + ",油站代码:" + stationCode + ",返回消息:" + responseMessage);
		}
	}

	/**
	 * 同步站级数据
	 * 
	 * @param btime
	 * @param etime
	 */
	public void syncStationData(String btime, String etime) {
		// 存储异常的IP地址
		List<String> IPErrorList = new ArrayList<String>();

		// 扫描所有的油站
		String stationsql = " SELECT station_code,ip,station_name,CONTROL_STATION_CODE FROM SH_STATION WHERE ip <> '' AND IP IS NOT NULL ";
		List<Object[]> stationIPList = JdbcTemplate.queryListFieldInfoByBean(DbConUtil.get162ptCon(), stationsql, null, DbConUtil.TYPE_NAME_162PT);
		for (Object[] stationInfo : stationIPList) {
			String appStationCode = stationInfo[0].toString();
			String ipAddress = stationInfo[1].toString();
			String stationName = stationInfo[2].toString();
			String gkStationCode = (stationInfo[3] == null || StringUtils.isBlank(stationInfo[3].toString())) ? appStationCode : stationInfo[3].toString();

			if (IPErrorList.contains(ipAddress)) {
				continue;
			}
			// 检测IP是否能连接上

			if (!DataExchangeUtil.isOkForIpAddressCon(ipAddress)) {
				// 这个IP地址重试3次,还是连不上数据库
				String message = "数据库连接失败,IP地址: " + ipAddress;
				insertLog(appStationCode, stationName, message, ipAddress);

				IPErrorList.add(ipAddress);
				continue;
			}

			// IP和油站代码是否一致

			if (!DataExchangeUtil.isOkForStationAndIP(ipAddress, gkStationCode)) {
				// IP地址维护错了(记录日志)
				String message = "IP地址和实际的油站代码不一致,IP地址: " + ipAddress;
				insertLog(appStationCode, stationName, message, ipAddress);

				IPErrorList.add(ipAddress);
				continue;
			}

			// 上传进货验收
			int jhysCount = syncJhys(ipAddress, appStationCode, gkStationCode, btime, etime);

			// 上传销售
			int saleCount = syncSaleData(ipAddress, appStationCode, gkStationCode, btime, etime);

			// 上传时点库存
			int sdStockCount = syncSdstockData(ipAddress, appStationCode, gkStationCode, btime, etime);

			// 上传整点库存
			int zdStockCount = syncZdstockData(ipAddress, appStationCode, gkStationCode, btime, etime);

			// 记录日志
			String message = "进货验收数据:" + jhysCount + "条, 销售数据:" + saleCount + "条, 时点库存数据:" + sdStockCount + "条, 整点库存数据:" + zdStockCount + "条";
			insertLog(appStationCode, stationName, message, ipAddress);

		}
	}

	private void insertLog(String appStationCode, String stationName, String message, String ipAddress) {

		String logSql = " INSERT INTO all_sync_logs VALUES ('" + appStationCode + "','" + stationName + "','" + message + "',GETDATE(), '" + ipAddress + "')  ";

		JdbcTemplate.insertByBean(DbConUtil.get162testCon(), logSql, null, DbConUtil.TYPE_NAME_162TEST);
	}

	// 处理进货验收数据
	private int syncJhys(String ipAddress, String appStationCode, String gkStationCode, String btime, String etime) {
		String sql = "SELECT b.DeliveryNo deliveryNob,b.CreateTime createTimeb,b.IsDel isDelb,b.DuringSales duringSalesb,b.TranStatus tranStatusb,b.BeginTime beginTimeb,b.EndTime endTimeb,b.*,a.* FROM oss_acceptance_odRegister a LEFT JOIN oss_acceptance_odRegisterInfo b on a.ManualNo = b.ManualNo where 1=1 ";
		sql += " and a.EndTime IS NOT NULL and DATE_FORMAT(b.EndTime,'%Y-%m-%d') >= '" + btime + "' and DATE_FORMAT(b.EndTime,'%Y-%m-%d') <= '" + etime
				+ "' ORDER BY b.EndTime DESC ";
		List<JhysBean> jhysInfoList = JdbcTemplate.queryListInfoByFields(DbConUtil.getGsmappCon(ipAddress), sql, JhysBean.class, null, ipAddress);
		if (jhysInfoList == null) {
			return 0;
		}

		List<JhysIsOk> jhysIsOKList = selectAppCenterJhysIsOkList(appStationCode);

		List<String> jhysIsOkNoList = new ArrayList<String>();
		for (JhysIsOk jhys : jhysIsOKList) {
			jhysIsOkNoList.add(jhys.getDeliveryno());
		}

		List<String> deliveryNoList1 = new ArrayList<String>();
		final List<BfJyysJson> jhysListJson = new ArrayList<BfJyysJson>();

		for (JhysBean jhysBean : jhysInfoList) {
			if (deliveryNoList1.contains(jhysBean.getDeliveryNo()) || jhysIsOkNoList.contains(jhysBean.getDeliveryNo())) {
				continue;
			}

			BfJyysJson bfjson = new BfJyysJson();

			BfJyys jhys = DataExchangeUtil.jhysJsonSetValue(gkStationCode, jhysBean);

			bfjson.setOdRegister(jhys);

			List<BfJyysDetail> jhysDetailList = new ArrayList<BfJyysDetail>();

			for (JhysBean jhysBean1 : jhysInfoList) {
				if (jhysBean.getDeliveryNo().equals(jhysBean1.getDeliveryNob())) {

					BfJyysDetail jhysDetail = DataExchangeUtil.jhysDetailJsonSetValue(jhysBean1);

					jhysDetailList.add(jhysDetail);
				}
			}

			bfjson.setRegisterInfos(jhysDetailList);
			deliveryNoList1.add(jhysBean.getDeliveryNo());
			jhysListJson.add(bfjson);
		}

		// jhysListJson中的数据量可能会很大,容易造成接口程序堵塞,所以将数据分成小块多次发送
		new Thread() {
			@Override
			public void run() {
				// 调用进货验收接口发送数据
				DataExchangeUtil.sendJhysData(jhysListJson, "TimeTaskJobUtil");
			}
		}.start();

		return jhysListJson.size();
	}

	private List<JhysIsOk> selectAppCenterJhysIsOkList(String stationCode) {
		// 该站在平台上已经完成进货验收的出库单
		List<JhysIsOk> jhysIsOKList = JdbcTemplate.queryListInfoByFields(DbConUtil.get162ptCon(), "SELECT nodeno,deliveryno FROM BF_JYYS WHERE etime is NOT NULL AND nodeno = '"
				+ stationCode + "' ", JhysIsOk.class, null, DbConUtil.TYPE_NAME_162PT);
		return jhysIsOKList;
	}

	// 上传时点库存数据
	private int syncSdstockData(String ipAddress, String appStationCode, String gkStationCode, String btime, String etime) {
		String selectSql = " SELECT * FROM oss_monitor_TimeInventory WHERE  DATE_FORMAT(StoreTime,'%Y-%m-%d') >='" + btime + "' AND DATE_FORMAT(StoreTime,'%Y-%m-%d') <= '" + etime
				+ "' ";
		List<OssMonitorTimeInventory> sdstockList = JdbcTemplate.queryListInfoByBean(DbConUtil.getGsmappCon(ipAddress), selectSql, OssMonitorTimeInventory.class, null, ipAddress);
		if (sdstockList == null) {
			return 0;
		}

		final List<StockData> sdStockJsonList = new ArrayList<StockData>();
		for (OssMonitorTimeInventory sdstock : sdstockList) {
			StockData sdstockJson = DataExchangeUtil.sdStockJsonSetValue(gkStationCode, sdstock);
			sdStockJsonList.add(sdstockJson);
		}
		new Thread() {
			@Override
			public void run() {
				DataExchangeUtil.sendSdStockData(sdStockJsonList);
			}
		}.start();

		return sdStockJsonList.size();
	}

	public List<AppCenterSdstockBean> selectAppCenterSdstockData(String appStationCode) {
		String sql = " SELECT nodeno,oilcan,storetime FROM BF_STOCK_HOUT_DATA WHERE nodeno = '" + appStationCode + "' ";
		List<AppCenterSdstockBean> appCenterSdstockList = JdbcTemplate.queryListInfoByBean(DbConUtil.get162ptCon(), sql, AppCenterSdstockBean.class, null,
				DbConUtil.TYPE_NAME_162PT);
		return appCenterSdstockList;
	}

	// 上传销售数据
	private int syncSaleData(String ipAddress, String appStationCode, String gkStationCode, String btime, String etime) {
		String saleSql = " SELECT * FROM oss_daily_TradeInventory WHERE DATE_FORMAT(TakeDate,'%Y-%m-%d') >='" + btime + "' AND DATE_FORMAT(TakeDate,'%Y-%m-%d') <= '" + etime
				+ "' ";
		List<OssDailyTradeInventory> saleList = JdbcTemplate.queryListInfoByBean(DbConUtil.getGsmappCon(ipAddress), saleSql, OssDailyTradeInventory.class, null, ipAddress);
		if (saleList == null) {
			return 0;
		}

		final List<Sale> saleJsonList = new ArrayList<Sale>();
		for (OssDailyTradeInventory sale : saleList) {
			Sale saleJson = DataExchangeUtil.saleJsonSetValue(gkStationCode, sale);
			saleJsonList.add(saleJson);
		}

		new Thread() {
			@Override
			public void run() {
				DataExchangeUtil.sendSaleData(saleJsonList);
			}
		}.start();
		return saleJsonList.size();
	}

	// 上传整点库存数据
	private int syncZdstockData(String ipAddress, String appStationCode, String gkStationCode, String btime, String etime) {
		String beginTime = btime.replaceAll("-", "");
		String endTime = etime.replaceAll("-", "");
		String zdSql = " SELECT * FROM oss_monitor_Inventory WHERE DATE >= '" + beginTime + "' AND DATE <= '" + endTime + "' ";
		List<OssMonitorInventory> zdStockList = JdbcTemplate.queryListInfoByBean(DbConUtil.getGsmappCon(ipAddress), zdSql, OssMonitorInventory.class, null, ipAddress);
		if (zdStockList == null) {
			return 0;
		}

		final List<ZdStock> zdStockJsonList = new ArrayList<ZdStock>();

		for (OssMonitorInventory zdStock : zdStockList) {
			ZdStock zdJson = DataExchangeUtil.zdStockJsonSetValue(gkStationCode, zdStock);
			zdStockJsonList.add(zdJson);
		}

		new Thread() {
			@Override
			public void run() {
				DataExchangeUtil.sendZdStockData(zdStockJsonList);
			}
		}.start();

		return zdStockJsonList.size();

	}

	public void syncSjfyData(String time) {
		// 取到实际发油数据
		String sjfySql = " SELECT top 100 * FROM SH_SJFY WHERE (ispush IS NULL OR ispush = '') ";
		if (StringUtils.isNotBlank(time)) {
			sjfySql += " AND CONVERT(VARCHAR,inserttime,23) = '" + time + "' ";
		}

		List<Object[]> sjfyList = JdbcTemplate.queryListFieldInfoByBean(DbConUtil.get162ptCon(), sjfySql, null, DbConUtil.TYPE_NAME_162PT);
		for (Object[] sjfy : sjfyList) {
			Object ckdId = sjfy[0] == null ? "" : sjfy[0];
			Object sjfyl = sjfy[1] == null ? "" : sjfy[1];
			Object wd = sjfy[2] == null ? 0 : sjfy[2];
			if (StringUtils.isBlank(ckdId.toString())) {
				continue;
			}
			// 查询出库单的油站代码
			String ckdStationCodeSql = " SELECT  station_code FROM SH_CKD WHERE ck_id = '" + ckdId + "' ";
			Object[] ckdInfo = JdbcTemplate.queryFieldInfoByBean(DbConUtil.get162ptCon(), ckdStationCodeSql, null, DbConUtil.TYPE_NAME_162PT);
			if (ckdInfo == null || ckdInfo[0] == null || StringUtils.isBlank(ckdInfo[0].toString())) {
				continue;
			}
			String appStationCode = ckdInfo[0].toString();
			String gkStationCode = DataExchangeUtil.getGkStationCode(appStationCode);
			String ipAddress = DataExchangeUtil.getIpAddressByStationCode(appStationCode);
			if (StringUtils.isBlank(ipAddress)) {
				updateSjfyStatus(ckdId, ConstantUtil.SJFY_NOT_SEND);
				continue;
			}
			// 检测IP能否连上
			if (!DataExchangeUtil.isOkForIpAddressCon(ipAddress)) {
				log.info("数据库连接失败, IP地址: " + ipAddress);
				continue;
			}

			if (!DataExchangeUtil.isOkForStationAndIP(ipAddress, gkStationCode)) {
				log.info("IP地址与实际油站代码不一致,油站代码: " + appStationCode + ",IP地址: " + ipAddress);
				continue;
			}

			// 写入站级系统
			String selectStationSql = " SELECT * FROM oss_sysmanage_Realgiveoil WHERE ckdId = '" + ckdId + "' ";
			String insertStationSql = " INSERT INTO oss_sysmanage_Realgiveoil(ckdId,sjfyl,wd,md) VALUES ('" + ckdId + "','" + sjfyl + "'," + wd + ", 0)  ";

			Object[] stationSjfyInfo = JdbcTemplate.queryFieldInfoByBean(DbConUtil.getGsmappCon(ipAddress), selectStationSql, null, ipAddress);
			if (stationSjfyInfo == null) {
				boolean isInsertOk = JdbcTemplate.insertByBean(DbConUtil.getGsmappCon(ipAddress), insertStationSql, null, ipAddress);
				if (isInsertOk) {
					updateSjfyStatus(ckdId, ConstantUtil.SJFY_ALREAD_SEND);
				}
			} else {
				updateSjfyStatus(ckdId, ConstantUtil.SJFY_ALREAD_EXISTED);
			}

			// 写入北分中心系统
			Object[] bfSjfyInfo = JdbcTemplate.queryFieldInfoByBean(DbConUtil.getGsmcenterCon(), selectStationSql, null, DbConUtil.TYPE_NAME_GSMCENTER);
			if (bfSjfyInfo == null) {
				JdbcTemplate.insertByBean(DbConUtil.getGsmcenterCon(), insertStationSql, null, DbConUtil.TYPE_NAME_GSMCENTER);
			}
		}
	}

	private void updateSjfyStatus(Object ckdId, int status) {
		// 将标示设置成-1
		String updateSjfyFail = " UPDATE SH_SJFY SET ispush = " + status + " WHERE CKD_ID = '" + ckdId + "' ";
		JdbcTemplate.updateByBean(DbConUtil.get162ptCon(), updateSjfyFail, null, DbConUtil.TYPE_NAME_162PT);
	}

	public static void main(String[] args) {
		TimeTaskJobUtil job = new TimeTaskJobUtil();
		job.syncSjfyData("2017-03-20");
	}
}
